1.1. Field of the Invention
The present invention relates to electronic databases. In particular, it relates to a method and respective system for monitoring and analyzing a database performance problem with multi dimensional database models.
1.2. Description and Disadvantages of Prior Art
FIG. 1 illustrates a prior art system architecture with the basic functional and structural elements for monitoring and analyzing a database performance problem.
A rectangle represents a component of a database performance monitoring environment. An arrow illustrates the flow of information between system components and within a system component respectively; a database and a data store in general is represented by a cake symbol.
A database management system (DBMS) 101 manages one or more (N) databases. Both the database management system itself and each individual database (DB) can be considered as a monitoring object with respect to database performance monitoring.
A database management system has an instrumentation interface 102. The instrumentation interface enables database performance monitoring tools to access current performance metrics, for example performance counters, DBMS configuration parameters or DB configuration parameters in a standardized manner.
A database performance monitor 103 retrieves current performance metrics using the instrumentation interface. Once the performance metrics are retrieved they can be accessed by all components of the database performance monitor for further processing. The database performance monitor stores the performance metrics including derived performance metrics, like for example an average response time of SQL statements in the last week, in a performance database 104. Depending on how up-to-date the performance data is, one distinguishes a short-term and a long-term performance database:
The performance monitor stores the performance metrics in the short-term performance database after retrieving them via the instrumentation interface. The performance data is stored in the short-term performance database for a user-defined time interval, e.g., 48 hours. When the user defined time interval is reached the performance data is deleted from the short-term performance database.
The short term performance data is transferred to the long term performance database on a regular basis. The schedule of this transfer process can be defined by the user, e.g., every hour new short term performance data is transferred to the long-term performance database. The short-term performance data is aggregated during the transfer to the long-term performance database. Depending on the type of the performance data the aggregation consists of calculations like interval processing, average calculation, delta processing, etc.
An exception processing component 105 facilitates the performance data in the short term performance database to perform checks of individual performance metrics against user-defined metric thresholds on a regular basis. If a performance metric exceeds (or falls below) a user-defined threshold, a respective threshold exception is generated and logged in the exception log. The logging of a threshold exception can optionally trigger a user defined action, e.g., the notification of a database administrator (DBA) via email.
A workflow engine 106 is responsible for executing tasks stored in a workflow task database 107 on a scheduled basis. The following tasks are usually initiated by the workflow engine 106:
The retrieval of the performance metrics of the monitored object via the instrumentation interface,
the removal of the out-dated performance data from the short-term performance database,
the transfer—including aggregation—of the short term performance data to the long term performance database, check of regular exception processing, and
user-defined, repetitive tuning tasks, e.g., the generation of performance reports based on performance data stored in the long term performance database.
Usually a performance database is based on a classic relational database model. As to prior art, experienced database administrators (DBA) use performance databases to predict performance trends and to perform retrospective performance problem determination. A DBA can analyze current performance problems or performance problems of the past using the performance data that is stored in the performance database.
The information stored in performance databases is mainly analyzed using SQL queries in combination with tools that enable users to manage SQL queries.
Not all current performance monitoring tools support the administration of SQL queries accessing the performance database.
While relational database models are able to store huge amounts of data efficiently and without redundancy they tend to have a large number of tables to comply with normal forms defined by Codd. Besides relational data, models usually model the application domain and are not problem-specific. Both characteristics make it hard for SQL query authors to explore the data interactively for problem determination, as the logical coupling between redundancy-free storage and problem-specific storage is not stored and ready for being processed by a program.